﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace EasyCourse.Data.SqlServer
{
    public class SqlServerManager
    {
        public SqlServerManager()
        {
            this.ConnectionStrings = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=CourseSystem;Data Source=DESKTOP-98CR7E0";
        }

        public SqlServerManager(string connectionStrings)
        {
            this.ConnectionStrings = connectionStrings;
        }

        public string ConnectionStrings { get; set; }

        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionStrings))
            {
                connection.Open();
                using (var command = new SqlCommand(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                        command.Parameters.AddRange(parameters);
                    return command.ExecuteNonQuery();
                }
            }
        }

        public object ExcuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionStrings))
            {
                connection.Open();
                using (var command = new SqlCommand(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                        command.Parameters.AddRange(parameters);
                    return command.ExecuteScalar();
                }
            }
        }

        public DataTable GetData(string sql, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionStrings))
            {
                connection.Open();
                using (var adapter = new SqlDataAdapter(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                        adapter.SelectCommand.Parameters.AddRange(parameters);
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
        }

        public List<T> GetModels<T>(string sql, params SqlParameter[] parameters) where T : new()
        {
            using (var connection = new SqlConnection(ConnectionStrings))
            {
                connection.Open();
                using (var adapter = new SqlDataAdapter(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                        adapter.SelectCommand.Parameters.AddRange(parameters);
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    var handler = new ModelHandler<T>();
                    return handler.FillModel(dataTable);
                }
            }
        }

        public T GetModel<T>(string sql, params SqlParameter[] parameters) where T : new()
        {
            using (var connection = new SqlConnection(ConnectionStrings))
            {
                connection.Open();
                using (var adapter = new SqlDataAdapter(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                        adapter.SelectCommand.Parameters.AddRange(parameters);
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    var handler = new ModelHandler<T>();
                    if (dataTable.Rows.Count > 0)
                        return handler.FillModel(dataTable.Rows[0]);
                    return default;
                }
            }
        }
    }
}
